package cn.doit.sparkcore2json

import java.util.Properties

import com.typesafe.config.ConfigFactory
import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SQLContext

/**
  * Created by ZHAOXUHUA 
  * .           on 2018/11/29.
  */
object AreaTargetCountSQL {
  def main(args: Array[String]): Unit = {


  val conf = new SparkConf()
    .setMaster("local[*]")
    .setAppName(this.getClass.getSimpleName)
  val sc = new SparkContext(conf)
  //操作DataFrame必须有一个SQLContext的实例
  val sqlContext = new SQLContext(sc)
  val file = sqlContext.read.parquet("D:/学习专用/项目/项目四资料/data/dophinData")
  file.registerTempTable("t_AreaTargetCountSQL")
  //sparksql 提供了用户自定义函数功能udf
  sqlContext.udf.register("GGUDF",(bool:Boolean,right:Double,wrong:Double)=>if(bool) right else wrong)
  // 原始请求originalReq  注意and小写,最后不要加逗号,注意and不能用逗号代替
   val result = sqlContext.sql(
    """
select substring(requestdate, 0, 10) day, provincename, cityname,
sum(case when requestmode = 1 and processnode >= 1 then 1 else 0 end) originalReq,
sum(case when requestmode = 1 and processnode >= 2 then 1 else 0 end) validReq,
sum(case when requestmode = 1 and processnode >= 3 then 1 else 0 end) advReq,

sum(GGUDF(iseffective=1 and isbilling=1 and isbid=1 and adorderid!=0, 1, 0)) partiBid,
sum(if (iseffective=1 and isbilling=1 and iswin=1, 1, 0)) succBid,

sum(if (requestmode=2 and iseffective=1,1,0)) showT,
sum(if (requestmode=3 and iseffective=1,1,0)) cliT,

sum(if (iseffective=1 and isbilling=1 and isbid=1,winprice/1000,0)) dspAdvConsume,
sum(if (iseffective=1 and isbilling=1 and isbid=1,adpayment/1000,0)) dspAdvCost

from t_AreaTargetCountSQL
group by substring(requestdate, 0, 10), provincename, cityname

    """.stripMargin)
  val config = ConfigFactory.load()
  val url = config.getString("dolphin.mysql.url")
  val conn = new Properties()
  conn.setProperty("user", config.getString("dolphin.mysql.username"))
  conn.setProperty("password", config.getString("dolphin.mysql.password"))
  conn.setProperty("driver", config.getString("dolphin.mysql.driver"))

  result.write.jdbc(url, "dolphin_table4", conn)


  sc.stop()
  }
  //自己写的lowB代码
  /*  private val sql1 = sqlContext.sql(
      """
  select substring(requestdate, 0, 10) date, provincename, cityname, count(1) cnt
  t_AreaTargetCountSQL
  where requestmode == 1 And processnode >= 1
  group by provincename,cityname, substring(requestdate, 0, 10)
            """.stripMargin)

    //有效请求validReq
    private val sql2 = sqlContext.sql(
      """
  select substring(requestdate, 0, 10) date, provincename, cityname, count(1) cnt
  t_AreaTargetCountSQL
  where requestmode == 1 And processnode >= 2
  group by provincename,cityname, substring(requestdate, 0, 10)
            """.stripMargin)

    //广告请求advReq
    private val sql3 = sqlContext.sql(
      """
  select substring(requestdate, 0, 10) date, provincename, cityname, count(1) cnt
  t_AreaTargetCountSQL
  where requestmode == 1 And processnode == 3
  group by provincename,cityname, substring(requestdate, 0, 10)
            """.stripMargin)
    //参与成功partiBid
    private val sql4 = sqlContext.sql(
      """
  select substring(requestdate, 0, 10) date, provincename, cityname, count(1) cnt
  t_AreaTargetCountSQL
  where iseffective == 1 And isbilling == 1 And isbid == 1 And adorderid !=0
  group by provincename,cityname, substring(requestdate, 0, 10)
            """.stripMargin)

    //竞价成功succBid
    private val sql5 = sqlContext.sql(
      """
  select substring(requestdate, 0, 10) date, provincename, cityname, count(1) cnt
  t_AreaTargetCountSQL
  where iseffective == 1 And isbilling == 1 And iswin == 1
  group by provincename,cityname, substring(requestdate, 0, 10)
            """.stripMargin)

    //展示showT
    private val sql6 = sqlContext.sql(
      """
  select substring(requestdate, 0, 10) date, provincename, cityname, count(1) cnt
  t_AreaTargetCountSQL
  where requestmode == 2 And iseffective == 1
  group by provincename,cityname, substring(requestdate, 0, 10)
            """.stripMargin)

    //点击cliT
    private val sql7 = sqlContext.sql(
      """
  select substring(requestdate, 0, 10) date, provincename, cityname, count(1) cnt
  t_AreaTargetCountSQL
  where requestmode == 3 And iseffective == 1
  group by provincename,cityname, substring(requestdate, 0, 10)
            """.stripMargin)

    //广告消费dspAdvConsume
    private val sql8 = sqlContext.sql(
      """
  select substring(requestdate, 0, 10) date, provincename, cityname, count(1) cnt
  t_AreaTargetCountSQL
  where iseffective == 1 And isbilling == 1 And isbid == 1
  group by provincename,cityname, substring(requestdate, 0, 10)
            """.stripMargin)

    //广告成本dspAdvCost
    private val sql9 = sqlContext.sql(
      """
  select substring(requestdate, 0, 10) date, provincename, cityname, count(1) cnt
  t_AreaTargetCountSQL
  where iseffective == 1 And isbilling == 1 And isbid == 1
  group by provincename,cityname, substring(requestdate, 0, 10)
            """.stripMargin)*/

}
